﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using System.Data.OleDb;//包含对EXCEL文件进行连接或操作的类，EXCEL文件也是一种数据源

namespace TextMIS
{
    public partial class frmDataPutIn : Form
    {
        public frmDataPutIn()
        {
            InitializeComponent();
        }

        SqlConnection conn;
        SqlCommand com;
        DataTable dt;//存放导入的数据

        private void frmDataPutIn_Load(object sender, EventArgs e)
        {

        }

        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn1 = new OleDbConnection(strConn);
            string strExcel = "";
            OleDbDataAdapter da1 = null;
            DataSet ds = null;
            strExcel = "select 项目编号,项目名称,所属科室,价格,备注 from [Sheet1$]";
            da1 = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            if (conn1.State == ConnectionState.Closed)
            {
                conn1.Open();
            }
            da1.Fill(ds);
            conn1.Close();
            return ds;
        }

        private void tsbPutIn_Click(object sender, EventArgs e)
        {
            string fileName = "";
            try
            {
                openFileDialog1.Filter = "Excel文件|*.xls";
                openFileDialog1.ShowDialog();

                fileName = openFileDialog1.FileName.ToString();

            }
            catch (Exception ex)
            {
                MessageBox.Show("打开文件出错！" + ex.Message.ToString());
            }
            try
            {

                if (fileName != "")//如果已选了EXCEL文件
                {
                    DataSet ds = ExcelToDS(fileName);
                    dt = new DataTable();
                    dt = ds.Tables[0];
                    this.dgvItem.DataSource = dt;//将数据在表格中显示
                    tsbSave.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入文件出错！" + ex.Message.ToString());
            }
        }

        private void tsbSave_Click(object sender, EventArgs e)
        {
            conn = new
             SqlConnection(frmLogin.SQLstr);
            com = conn.CreateCommand();
            com.CommandText = "insert into ItemInfo(ItemNo,ItemName,BranchName,ItemPrice,ItemDetails) values(@ItemNo,@ItemName,@BranchName,@ItemPrice,@ItemDetails)";
            com.Parameters.Add(new SqlParameter("@ItemNo", SqlDbType.NChar));
            com.Parameters.Add(new SqlParameter("@ItemName", SqlDbType.NVarChar));
            com.Parameters.Add(new SqlParameter("@BranchName", SqlDbType.NChar));
            com.Parameters.Add(new SqlParameter("@ItemPrice", SqlDbType.NVarChar));
            com.Parameters.Add(new SqlParameter("@ItemDetails", SqlDbType.NVarChar));

            try
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    com.Parameters["@ItemNo"].Value = dt.Rows[i][0].ToString();
                    com.Parameters["@ItemName"].Value = dt.Rows[i][1].ToString();
                    com.Parameters["@BranchName"].Value = dt.Rows[i][2].ToString();
                    com.Parameters["@ItemPrice"].Value = dt.Rows[i][3].ToString();
                    com.Parameters["@ItemDetails"].Value = dt.Rows[i][4].ToString();
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    com.ExecuteNonQuery();
                    conn.Close();
                }
                MessageBox.Show("已成功将EXCEL数据导入到数据库中！");
                dgvItem.DataSource = null;
                dgvItem.ReadOnly = true;
            }

            catch (Exception ex)
            {
                MessageBox.Show("保存数据有误！　" + ex.Message.ToString());
            }
        }

        private void tsbExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }


    }
}
